package com.yfqy.admin.util;

import com.yfqy.admin.domain.entity.RevenueRecord;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtils {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtils.class);

    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    // 定义Excel列名与实体类字段的映射关系
    private static final Map<String, String> COLUMN_MAPPING = new HashMap<>() {{
        put("日期", "recordTime");
        put("初诊", "theFirst");
        put("复诊", "theFirst");
        put("治疗师", "therapist");
        put("顾客姓名", "customerName");
        put("项目", "project");
        put("项目明细", "projectDetail");
        put("备注", "remarks");
        put("电话号码", "phone");
        put("微信号码", "wechat");
    }};

    // 支付方式列名（需要合并的列）
    private static final List<String> PAYMENT_COLUMNS = Arrays.asList(
            "微信", "支付宝", "工行码", "pos机", "抖音", "美团/大众"
    );

    public static List<RevenueRecord> parseExcel(MultipartFile file) throws Exception {
        List<RevenueRecord> records = new ArrayList<>();

        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(2);
            Row headerRow = sheet.getRow(0);

            // 1. 构建列名 -> 列索引的映射
            Map<String, Integer> columnIndexMap = new HashMap<>();
            for (Cell cell : headerRow) {
                String columnName = cell.getStringCellValue().trim();
                if (COLUMN_MAPPING.containsKey(columnName) || PAYMENT_COLUMNS.contains(columnName)) {
                    columnIndexMap.put(columnName, cell.getColumnIndex());
                }
            }

            // 2. 遍历数据行（从第2行开始）
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                RevenueRecord record = new RevenueRecord();

                // 3. 解析基础字段
                setField(record, "recordTime", row.getCell(columnIndexMap.get("日期")));
                setField(record, "therapist", row.getCell(columnIndexMap.get("治疗师")));
                setField(record, "customerName", row.getCell(columnIndexMap.get("顾客姓名")));
                setField(record, "project", row.getCell(columnIndexMap.get("项目")));
                setField(record, "projectDetail", row.getCell(columnIndexMap.get("项目明细")));
                setField(record, "phone", row.getCell(columnIndexMap.get("电话号码")));
                setField(record, "wechat", row.getCell(columnIndexMap.get("微信号码")));
                setField(record, "remarks", row.getCell(columnIndexMap.get("备注")));

                // 4. 处理初诊/复诊（合并逻辑）
                Cell firstVisitCell = row.getCell(columnIndexMap.get("初诊"));
                LOGGER.info("初诊: {}", firstVisitCell);
                Cell returnVisitCell = row.getCell(columnIndexMap.get("复诊"));
                if (returnVisitCell != null && "√".equals(getCellStringValue(returnVisitCell))) {
                    record.setTheFirst((byte) 2);
                } else {
                    record.setTheFirst((byte) 1);
                }

                // 5. 合并支付方式与金额
                for (String paymentCol : PAYMENT_COLUMNS) {
                    Integer colIndex = columnIndexMap.get(paymentCol);
                    if (colIndex != null) {
                        Cell paymentCell = row.getCell(colIndex);
                        String amountStr = getCellStringValue(paymentCell);
                        if (!amountStr.isEmpty()) {
                            record.setPaymentType(paymentCol);
                            record.setPaymentAmount(new BigDecimal(amountStr));
                            break; // 只取第一个有值的支付方式
                        }
                    }
                }
                records.add(record);
            }
        }
        return records;
    }

    private static void setField(RevenueRecord record, String fieldName, Cell cell) {
        try {
            String value = getCellStringValue(cell);
            if (value.isEmpty()) return;

            if (fieldName.equals("recordTime")) {
                record.setRecordTime(cell.getDateCellValue());
            } else {
                // 其他String类型字段通过反射设置（简化代码）
                Field field = record.getClass().getDeclaredField(fieldName);
                field.setAccessible(true);
                field.set(record, value);
            }
        } catch (Exception e) {
            LOGGER.error(e.getMessage());
            throw new RuntimeException("设置字段 " + fieldName + " 失败", e);
        }
    }

    private static String getCellStringValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                // 处理日期类型
                if (DateUtil.isCellDateFormatted(cell)) {
                    return sdf.format(cell.getDateCellValue());
                } else {
                    // 普通数字类型
                    return BigDecimal.valueOf(cell.getNumericCellValue())
                            .stripTrailingZeros()
                            .toPlainString();
                }
            default:
                return "";
        }
    }
}